/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.me.mobisegua.adapter;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;


/**
 *
 * @author Zé Carlos
 */
public class MobiSegUAAdapter {

    private static final String TAG = "MobiSegUAAdapter";

    private static final String DATABASE_NAME = "MobiSegUA_1";
    private static final String DATABASE_TABLE1 = "Ronda";
    private static final String DATABASE_TABLE2 = "Area";
    private static final String DATABASE_TABLE3 = "Departamento";
    private static final String DATABASE_TABLE4 = "TipoObjecto";
    private static final String DATABASE_TABLE5 = "TipoOcorrencia";
    private static final String DATABASE_TABLE6 = "Ocorrencia";
    private static final int DATABASE_VERSION = 1;

    /** Ronda */
    private static final String TABLE1_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE1
            +" (_id integer primary key autoincrement, "
            + "SegNome text NOT NULL, "
            + "Dia text NOT NULL, "
            + "HoraInicio text NOT NULL, "
            + "HoraFim text, "
            + "Latitude text NOT NULL, "
            + "Longitude text NOT NULL);\n ";

    /** Area */
    private static final String TABLE2_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE2
            +" (_id integer primary key autoincrement, "
            + "Nome text NOT NULL, "
            + "Latitude text, "
            + "Longitude text );\n ";

    /** Departamento */
     private static final String TABLE3_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE3
            +" (_id integer primary key autoincrement, "
            + "Nome text NOT NULL, "
            + "RefIDArea int REFERENCES Area(_id), "
            + "Latitude text, "
            + "Longitude text);\n ";
            //+ "FOREIGN KEY (RefIDArea) REFERENCES Area(_id)); ";

     /** TipoObjecto */
   private static final String TABLE4_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE4
            +" (_id integer primary key autoincrement, "
            + "Nome text NOT NULL) ;\n ";

   /** TipoOcorrencia */
    private static final String TABLE5_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE5
            +" (_id integer primary key autoincrement, "
            + "Nome text NOT NULL, "
            + "RefIDObjecto int NOT NULL REFERENCES TipoObjecto(_id) );\n ";
            //+ "FOREIGN KEY (RefIDObjecto) REFERENCES TipoObjecto(_id) ); ";

     /** Ocorrencia */
    private static final String TABLE6_CREATE =
            "create table IF NOT EXISTS "+DATABASE_TABLE6
            +" (_id integer primary key autoincrement, "
            + "RefIDTipoObjecto int REFERENCES TipoObjecto(_id), "
            + "RefIDRonda int REFERENCES Ronda(_id), "
            + "RefIDDepartamento int REFERENCES Departamento(_id), "
            + "Andar int, "
            + "Img blob, "
            + "Desc text NOT NULL, "
            + "DescAudio blob);\n ";
          //  + "FOREIGN KEY (RefIDTipoObjecto) REFERENCES TipoObjecto(_id), "
          //  + "FOREIGN KEY (RefIDRonda) REFERENCES Ronda(_id), "
          //  + "FOREIGN KEY (RefIDDepartamento) REFERENCES Departamento(_id) ); ";



    private Context context = null;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    
    public MobiSegUAAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper
    {

        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL(TABLE1_CREATE);
            db.execSQL(TABLE2_CREATE);
            db.execSQL(TABLE3_CREATE);
            db.execSQL(TABLE4_CREATE);
            db.execSQL(TABLE5_CREATE);
            db.execSQL(TABLE6_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE1);
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE2);
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE3);
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE4);
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE5);
            db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE6);
            onCreate(db);
        }

    }

    // Step 3. Defining the various methods for opening and closing the database, as well as the methods for adding/editing/deleting rows in the table.

    /** Opens the database. */
    public MobiSegUAAdapter open() throws SQLException
    {

        db = DBHelper.getWritableDatabase();

        return this;
    }

    /** Closes the database. */
    public void close()
    {
        DBHelper.close();
    }


    /****************** Insert Methods. ***************************************/

    public long insertRonda(String secName, String date, String startTime, String endTime, String latitude, String longitude)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("SegNome", secName);
        initialValues.put("Dia", date);
        initialValues.put("HoraInicio", startTime);
        initialValues.put("HoraFim", endTime);
        initialValues.put("Latitude", latitude);
        initialValues.put("Longitude", longitude);
        return db.insert(DATABASE_TABLE1, null, initialValues);
    }

     public long insertArea(String name)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("Nome", name);
        return db.insert(DATABASE_TABLE2, null, initialValues);
    }

    public long insertArea(String name, String latitude, String longitude)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("Nome", name);
        initialValues.put("Latitude", latitude);
        initialValues.put("Longitude", longitude);
        return db.insert(DATABASE_TABLE2, null, initialValues);
    }

    public long insertDept(String name, int RefIDArea, String latitude, String longitude)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("Nome", name);
        initialValues.put("RefIDArea", RefIDArea);
        initialValues.put("Latitude", latitude);
        initialValues.put("Longitude", longitude);
        return db.insert(DATABASE_TABLE3, null, initialValues);
    }

    public long insertTipoObjecto(String name)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("Nome", name);
        return db.insert(DATABASE_TABLE4, null, initialValues);
    }

    public long insertTipoOcorrencia(String name, int RefIDObjecto)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("Nome", name);
        initialValues.put("RefIDObjecto", RefIDObjecto);
        return db.insert(DATABASE_TABLE5, null, initialValues);
    }

    public long insertOcorrencia(int RefIDRonda, int RefIDDept, int RefIDTipoObjecto, int andar, byte[] img, String desc, String desc_audio)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("RefIDRonda", RefIDRonda);
        initialValues.put("RefIDDepartamento", RefIDDept);
        initialValues.put("RefIDTipoObjecto", RefIDTipoObjecto);
        initialValues.put("Andar", andar);
        initialValues.put("Img", img);
        initialValues.put("Desc", desc);
        initialValues.put("DescAudio", desc_audio);
        return db.insert(DATABASE_TABLE6, null, initialValues);
    }


    /********************* Get Methods. ***************************************/

    public int getNumAreas()
    {
        Cursor c = db.rawQuery("SELECT _id FROM Area;",null);
        return c.getCount();
    }

    public Cursor getAreaIDbyName(String name)
    {
        return  db.query(true, DATABASE_TABLE2,
                        new String[] {"_id"},
                        "Nome" + "=" + name,
                        // other fields
                        null, null, null, null, null);
    }

    public Cursor getDeptIDbyName(String name)
    {
        return  db.query(true, DATABASE_TABLE3,
                        new String[] {"_id"},
                        "Nome" + "=" + name,
                        // other fields
                        null, null, null, null, null);
    }

    /*
    public Cursor getAddressLocation(String rowId)
    {
        return  db.query(DATABASE_TABLE,
                        new String[] {"latitude", "longitude"},
                        "_id" + "=" + rowId,
                        // other fields
                        null, null, null, null);
    }

    //---retrieves all the contacts---
    public Cursor getAllContacts()
    {
        return db.query(DATABASE_TABLE, new String[] {
        		"_id",
        		"name",
        		"address",
                        "phoneNumber",
                        "email",
                        "latitude",
                        "longitude"
                        },
                        /* Selection - All */
   //                     null,
                        /* Remaining parameters */
     //                   null,null,null,null);
//    }


}
